package com.sinosoft.plugin.code.pdm;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.nutz.log.Log;
import org.nutz.log.Logs;

import com.sinosoft.domain.code.CodeDbConfig;

/**
 * 根据DB链接获取表列表
 * @author unhappydepig
 *
 */

//1.TABLE_CAT        (String)   => 表所在的编目(可能为空)  
//2.TABLE_SCHEM (String)   => 表所在的模式(可能为空) 
//3.TABLE_NAME    (String)   => 表的名称
//4.TABLE_TYPE     (String)    => 表的类型。 典型的有 "TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL  TEMPORARY", "ALIAS", "SYNONYM". 
//5.REMARKS          (String)       => 解释性的备注
//6.TYPE_CAT          (String)      =>编目类型(may be null) 
//7.TYPE_SCHEM   (String)      => 模式类型(may be null) 
//8.TYPE_NAME      (String)      => 类型名称(may be null) 
//9.SELF_REFERENCING_COL_NAME    (String) => name of the designated "identifier" column of a typed table (may be null) 
//10.REF_GENERATION   (String)    => specifies how values in SELF_REFERENCING_COL_NAME are created. 它的值有："SYSTEM"、"USER"、"DERIVED"，也可能为空。

//1.TABLE_CAT String => table catalog (may be null)
//2.TABLE_SCHEM String => table schema (may be null)
//3.TABLE_NAME String => table name (表名称)
//4.COLUMN_NAME String => column name(列名)
//5.DATA_TYPE int => SQL type from java.sql.Types(列的数据类型)
//6.TYPE_NAME String => Data source dependent type name, for a UDT the type name is fully qualified
//7.COLUMN_SIZE int => column size.
//8.BUFFER_LENGTH is not used.
//9.DECIMAL_DIGITS int => the number of fractional digits. Null is returned for data types where DECIMAL_DIGITS is not applicable.
//10.NUM_PREC_RADIX int => Radix (typically either 10 or 2)
//11.NULLABLE int => is NULL allowed.
//12.REMARKS String => comment describing column (may be null)
//13.COLUMN_DEF String => default value for the column, (may be null)
//14.SQL_DATA_TYPE int => unused
//15.SQL_DATETIME_SUB int => unused
//16.CHAR_OCTET_LENGTH int => for char types the maximum number of bytes in the column
//17.ORDINAL_POSITION int => index of column in table (starting at 1)
//18.IS_NULLABLE String => ISO rules are used to determine the nullability for a column.
//19.SCOPE_CATLOG String => catalog of table that is the scope of a reference attribute (null if DATA_TYPE isn't REF)
//20.SCOPE_SCHEMA String => schema of table that is the scope of a reference attribute (null if the DATA_TYPE isn't REF)
//21.SCOPE_TABLE String => table name that this the scope of a reference attribure (null if the DATA_TYPE isn't REF)
//22.SOURCE_DATA_TYPE short => source type of a distinct type or user-generated Ref type, SQL type from java.sql.Types
//23.IS_AUTOINCREMENT String => Indicates whether this column is auto incremented

public class DBParser {
	private static Log log = Logs.get();
	private PDM pdm = new PDM();
	/**
	 * PDM格式化
	 * @param pdmFileName
	 * @return
	 * @throws Exception
	 */
	public PDM pdmParser(CodeDbConfig config) throws Exception{
		return pdmParser(config, null);
	}
	/**
	 * PDM格式化
	 * @param pdmFileName
	 * @return
	 * @throws Exception
	 */
	public PDM pdmParser(CodeDbConfig config,String table_key) throws Exception {
//		Dao dao = new NutzDaoUtil().getNutzDao(config);
		Connection connection = null;
		try {
			// 获取数据库连接
			Class.forName(config.getDbDriver());
			connection = DriverManager.getConnection(config.getDbAddress(), config.getDbUser(),
					config.getDbPass());
			DatabaseMetaData metaData = connection.getMetaData();
			pdm.setDBMSCode(metaData.getDatabaseProductName());
			pdm.setName(config.getConfigName());
//			log.info(metaData.getDatabaseProductName());
			ResultSet tableResultset = metaData.getTables(null, null,
					null, new String[] { "TABLE" });
			ArrayList<PDMTable> tableList = new ArrayList<PDMTable>();
			pdm.setTables(tableList);
			while (tableResultset.next()) {	
				PDMTable table= new PDMTable();
				table.setCode(tableResultset.getString("TABLE_NAME"));
				table.setName(tableResultset.getString("REMARKS"));
				if(null==table.getName()||"".equals(table.getName())){
					table.setName(table.getCode());
				}
				table.setId(table.getCode());	
				if(null==table_key||"".equals(table_key)){
					tableList.add(table);
				}else{
					String tableCode = table.getCode().toUpperCase();
					table_key = table_key.toUpperCase();
					if(tableCode.indexOf(table_key)>-1){
						tableList.add(table);
					}
					
				}		
				log.info(table.getCode());			
			}
			Map<String,PDMColumn> columnMap = new HashMap<String,PDMColumn>();
			for (PDMTable table : tableList) {
				ResultSet columnResultset = metaData.getColumns(null,null,table.getCode(),null);
				ArrayList<PDMColumn> columns = new ArrayList<PDMColumn>();
				table.setColumns(columns);
				while (columnResultset.next()) {	
					PDMColumn column= new PDMColumn();
					column.setCode(columnResultset.getString("COLUMN_NAME"));
					column.setName(columnResultset.getString("REMARKS"));
					column.setDataType(columnResultset.getString("TYPE_NAME"));
					column.setLength(columnResultset.getString("COLUMN_SIZE"));
					column.setPrecision(columnResultset.getString("DECIMAL_DIGITS"));
					if(!columnResultset.getBoolean("NULLABLE")){
						column.setMandatory("1");	
					} 
					column.setDefaultValue(columnResultset.getString("COLUMN_DEF"));
					if(null==column.getName()||"".equals(column.getName())){
						column.setName(column.getCode());
					}					
					columns.add(column);
					log.info(column.getCode());
					columnMap.put(column.getCode(), column);
				}
				ResultSet pkResultset = metaData.getPrimaryKeys(null,null,table.getCode());				
				ArrayList<PDMKey> keys = new ArrayList<PDMKey>();
				table.setKeys(keys);
				PDMKey  key = new PDMKey();
				key.setCode("pk_"+table.getCode());
				keys.add(key);
				ArrayList<PDMColumn> keyColumns = new ArrayList<PDMColumn>();
				key.setColumns(keyColumns);
				while (pkResultset.next()) {	
					String code = pkResultset.getString("COLUMN_NAME");
					keyColumns.add(columnMap.get(code));
				}
			}
				
		}catch (Exception e) {
			throw e;
		}
		return pdm;
	}
	
}
